﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;

namespace Facade.Excel
{
    public class ImportFacade
    {
        /// <summary>
        /// 将DataTable数据转换Excel内存数据流
        /// </summary>
        /// <param name="SourceTable">DataTable数据源</param>
        /// <returns>Excel内存数据流</returns>
        public static Stream RenderDataTableToExcel(DataTable SourceTable)
        {
            using (HSSFWorkbook workbook = new HSSFWorkbook())
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    using (Sheet sheet = workbook.CreateSheet())
                    {
                        Row headerRow = sheet.CreateRow(0);

                        // 转换列
                        foreach (DataColumn column in SourceTable.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        }

                        // 转换数据
                        int rowIndex = 1;

                        foreach (DataRow row in SourceTable.Rows)
                        {
                            Row dataRow = sheet.CreateRow(rowIndex);

                            foreach (DataColumn column in SourceTable.Columns)
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            }

                            rowIndex++;
                        }

                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;

                        return ms;
                    }
                }
            }
        }

        /// <summary>
        /// 将DataTable数据转换Excel文件
        /// </summary>
        /// <param name="SourceTable">DataTable数据源</param>
        /// <param name="FileName"></param>
        public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
        {
            using (MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream)
            {
                using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();

                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// 转换Excel文件到DataTable数据表
        /// </summary>
        /// <param name="ExcelFileStream">Excel内存数据流</param>
        /// <param name="SheetName">Excel单页名称</param>
        /// <param name="HeaderRowIndex">Excel标题行索引</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            using (HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream))
            {
                using (Sheet sheet = workbook.GetSheet(SheetName))
                {
                    DataTable table = new DataTable();

                    Row headerRow = sheet.GetRow(HeaderRowIndex);
                    int cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    int rowCount = sheet.LastRowNum;

                    for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                    {
                        Row row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    ExcelFileStream.Close();

                    return table;
                }
            }
        }

        /// <summary>
        /// 转换Excel文件到DataTable数据表
        /// </summary>
        /// <param name="ExcelFileStream">Excel内存数据流</param>
        /// <param name="SheetIndex">Excel单页索引</param>
        /// <param name="HeaderRowIndex">Excel标题行索引</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            using (HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream))
            {
                using (Sheet sheet = workbook.GetSheetAt(SheetIndex))
                {
                    DataTable table = new DataTable();

                    Row headerRow = sheet.GetRow(HeaderRowIndex);
                    int cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    int rowCount = sheet.LastRowNum;

                    for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                    {
                        Row row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }

                        table.Rows.Add(dataRow);
                    }

                    ExcelFileStream.Close();
                    return table;
                }
            }
        }

        /// <summary>
        /// 读取excel默认第一行为标头
        /// </summary>
        /// <param name="path">excel文档路径</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string path)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            Sheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            Row headerRow = sheet.GetRow(0) as Row;
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                Cell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                Row row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }

            //while (rows.MoveNext())
            //{
            //    HSSFRow row = (HSSFRow)rows.Current;
            //    DataRow dr = dt.NewRow();

            //    for (int i = 0; i < row.LastCellNum; i++)
            //    {
            //        HSSFCell cell = row.GetCell(i);


            //        if (cell == null)
            //        {
            //            dr[i] = null;
            //        }
            //        else
            //        {
            //            dr[i] = cell.ToString();
            //        }
            //    }
            //    dt.Rows.Add(dr);
            //}

            return dt;
        }
    }
}
